using System;
using System.Xml;
using System.IO;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

using System.Collections.Generic;
using System.Text;

namespace SqlStatementGenerator
{
    public class SqlScriptGenerator
    {
        public struct Column
        {
            public string    Name;
            public string    Type;
            public string    TypeName;
            public string    Default;
            public bool      AllowNull;
            public int       Size;
            public int       Precision;
            public int       Scale;
        }    	

        private static int    FILE_NAME_NDX=0;
        public  static int    FILE_ROWS=500000;
        public  static string Source="mssql";
        public  static string Target="mssql";
        public  static bool   dropifexist=false;
        public  static bool   createtable=false;
        private static string QuotationMarksStart="";
        private static string QuotationMarksEnd="";

        public static string user_id   ="";
        public static string password  ="";
        public static string database  ="";
        public static string servername="";
        public static string outputfile="";

        private static string cP_K_COLUMNS_NAME="";
        private static Dictionary<string, Column> oData = new Dictionary<string, Column>();
        private static Dictionary<string, string> _COLUMN_Data = new Dictionary<string, string>();
        private static Dictionary<string, string> _Type_Data = new Dictionary<string, string>();

        public static SqlConnection GetConnection(string sConnection, string sDatabase)
        {
            SqlConnection dbconnection = null;

            try
            {
                dbconnection = new SqlConnection(sConnection);
                dbconnection.Open();

                if (sDatabase.Trim() != string.Empty)
                {
                    dbconnection.ChangeDatabase(sDatabase);
                }
            }
            catch (System.InvalidOperationException e)
            {
                throw new Exception("GetConnection error: " + e.Message);
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception("GetConnection error2: " + e.Message);
            }

            return dbconnection;
        }

        public static DataTable LoadDataTable(string sConnection, string sDatabase, string sQuery)
        {
            SqlConnection connection = null;
            DataTable table = null;
            try
            {
                connection = GetConnection(sConnection, sDatabase);
                if (connection == null)
                {
                    return table;
                }

                SqlCommand cmd = new SqlCommand(sQuery, connection);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                DataTable dt = new DataTable();
                dt.Load(dr);

                table = dt;
            }
            catch (Exception exception)
            {
                throw new Exception("GetDatabases error: " + exception.Message);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return table;
        }

        public static void WriteFileContents(string sFilePath, string sContents, bool bOverwrite)
        {
            FileStream file = null;
            StreamWriter sw = null;

            try
            {

                if (FILE_NAME_NDX==0)
                {
                }else{
                    sFilePath=sFilePath.ToLower().Replace(".sql","-"+String.Format("{0:0000}",FILE_NAME_NDX)+".sql");
                }

                //Console.WriteLine(sFilePath);
                // make sure we're allowed to overwrite the file if it exists
                if (bOverwrite == true)
                {
                    if (File.Exists(sFilePath))
                    {
                        File.Delete(sFilePath);
                    }
                }

                // Specify file, instructions, and privelegdes
                if (bOverwrite)
                {
                    file = new FileStream(sFilePath, FileMode.Create, FileAccess.Write);
                }else
                {
                    if (File.Exists(sFilePath))
                    {
                        file = new FileStream(sFilePath, FileMode.Append , FileAccess.Write);
                    }else{
                        file = new FileStream(sFilePath, FileMode.Create, FileAccess.Write);
                    }
                }

                // Create a new stream to write to the file
                if (Target=="mysql")
                {
                    sw = new StreamWriter(file);
                }else{
                    sw = new StreamWriter(file,Encoding.UTF8);
                }

                // Write a string to the file
                sw.Write(sContents);
            }
            catch (Exception ex)
            {
                throw new Exception("WriteFileContents() failed with error: " + ex.Message);
            }
            finally
            {
                // Close StreamWriter
                if (sw != null)
                    sw.Close();

                // Close file
                if (file != null)
                    file.Close();
            }
        }
        public static ArrayList GetDatabaseTableColumns(string sConnection, string sDatabase, string sTableName,string cSql)
        {
            SqlConnection connection = null;
            ArrayList oAl = null;

            try
            {
                connection = GetConnection(sConnection, sDatabase);
                if (connection == null)
                    return oAl;

                SqlCommand mySqlCommand = connection.CreateCommand();
                if (cSql!= string.Empty)
                {
                    mySqlCommand.CommandText =cSql;
                }else{
                    mySqlCommand.CommandText ="SELECT * FROM "+sTableName;
                }

                //Console.WriteLine("CommandText:"+sTableName+" "+	mySqlCommand.CommandText);
		            //Console.WriteLine(sTableName);

                SqlDataReader productsSqlDataReader =mySqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
                DataTable myDataTable = productsSqlDataReader.GetSchemaTable();
                cP_K_COLUMNS_NAME="";
                oAl = new ArrayList();
                foreach (DataRow myDataRow in myDataTable.Rows)
                {
                    //Console.WriteLine("\nNew column details follow:"); GetOleDbSchemaTable 
                    bool IsKey=false;
                    string cCOL="";
                    int    _NumericPrecision=0;
                    int    _ColumnSize=0;
                    int    _NumericScale=0;
                    bool   _AllowDBNull=true;
                    string _DataType="";
                    string BaseTableName="";
                    string DataTypeName="";
                    //Console.WriteLine("===================================================");
                    foreach (DataColumn myDataColumn in myDataTable.Columns)
                    {
                    	
                    		//Console.WriteLine(myDataColumn.ToString()+"="+myDataRow[myDataColumn]);
                        if (myDataColumn.ToString() == "BaseColumnName")
                        {
                            oAl.Add(myDataRow[myDataColumn]);
                            cCOL=myDataRow[myDataColumn].ToString();
                        }else if (myDataColumn.ToString() == "IsKey")
                        {
                            IsKey=(myDataRow[myDataColumn].ToString()=="True");
                        }else if (myDataColumn.ToString() == "BaseTableName")
                        {
                            BaseTableName=myDataRow[myDataColumn].ToString();
                            sTableName=BaseTableName;
                        }else if (myDataColumn.ToString() == "DataTypeName")
                        {
                            DataTypeName=myDataRow[myDataColumn].ToString();
                        }else if (myDataColumn.ToString() == "NumericPrecision")
                        {
                            try
                            {
                                _NumericPrecision = Convert.ToInt32(myDataRow[myDataColumn]);
                            }
                            catch
                            {
                                _NumericPrecision = 0;
                            }
                        }else if (myDataColumn.ToString() == "NumericScale")
                        {
                            try
                            {
                                _NumericScale = Convert.ToInt32(myDataRow[myDataColumn]);
                            }
                            catch
                            {
                                _NumericScale = 0;
                            }
                        }else if (myDataColumn.ToString() == "ColumnSize")
                        {
                            try
                            {
                                _ColumnSize = Convert.ToInt32(myDataRow[myDataColumn]);
                            }
                            catch
                            {
                                _ColumnSize = 0;
                            }
                        }else if (myDataColumn.ToString() == "AllowDBNull")
                        {
                            try
                            {
                                _AllowDBNull = bool.Parse(myDataRow[myDataColumn].ToString());
                            }
                            catch
                            {
                                _AllowDBNull = true;
                            }

                        }else if (myDataColumn.ToString() == "DataType")
                        {
                            _DataType=myDataRow[myDataColumn].ToString();
                        }
                    }

                    Column oColumn;

                    oColumn.Name=cCOL;
                    oColumn.Type=_DataType;
                    oColumn.AllowNull=_AllowDBNull;
                    oColumn.TypeName=DataTypeName;
                    oColumn.Default="";
                    oColumn.Size=_ColumnSize;
                    oColumn.Precision=_NumericPrecision;
                    oColumn.Scale=_NumericScale;

                    oData[BaseTableName+"_"+cCOL]=oColumn;


            
                    if (IsKey)
                    {
                        if (cP_K_COLUMNS_NAME!="")
                        {
                            cP_K_COLUMNS_NAME=cP_K_COLUMNS_NAME+",";
                        }
                        cP_K_COLUMNS_NAME=cP_K_COLUMNS_NAME+cCOL;
                    }

                }
                productsSqlDataReader.Close();

								OleDbConnection cn = new OleDbConnection();
		            //Connect to the Northwind database in SQL Server.
		            //Be sure to use an account that has permission to retrieve table schema.
		            cn.ConnectionString = "Provider=SQLOLEDB;Data Source="+servername+";User ID="+user_id+";Password="+password+";Initial Catalog="+database+"";
		            //Console.WriteLine(sTableName+" "+cn.ConnectionString);
		            cn.Open();
		            DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] {null, null, sTableName});
		            //List the schema info for the Employees table
		            //in the format Field Descriptor :Field Value.
		            for (int r=0;r<schemaTable.Rows.Count;r++)
		            {
		            	//Console.WriteLine(schemaTable.Rows[r]["COLUMN_DEFAULT"]);
			            string cDefault="";
			            string cCOLUMN_NAME="";
			            for (int i = 0; i < schemaTable.Columns.Count; i++) 
			            {
			            	if ("COLUMN_DEFAULT"==schemaTable.Columns[i].ToString())
			            	{
			            			cDefault=schemaTable.Rows[r][i].ToString();
			            		//Console.WriteLine(schemaTable.Columns[i].ToString() + " : " + schemaTable.Rows[r][i].ToString());
			            	}else if ("COLUMN_NAME"==schemaTable.Columns[i].ToString())
			            	{
			            		cCOLUMN_NAME=schemaTable.Rows[r][i].ToString();
			            		//Console.WriteLine(schemaTable.Columns[i].ToString() + " : " + schemaTable.Rows[r][i].ToString());
			            	}
			            	
			            }
			            if (cCOLUMN_NAME!="" && cDefault!="")
			          	{
		            			Column oColumn=oData[sTableName+"_"+cCOLUMN_NAME];
		            			oColumn.Default=cDefault;
		            			oData[sTableName+"_"+cCOLUMN_NAME]=oColumn;
			           	}
			            
			            //Console.WriteLine("--------------------------------------");
		          	}
		
		            //Explicitly close - don't wait on garbage collection.
		            cn.Close();
		            //Pause

            }
            catch (Exception ex)
            {
    						string cMessage = "<HR>Message=[" + ex.Message + "]" + "<HR>Source=[" + ex.Source + "]<HR>StackTrace=[" + ex.StackTrace + "]<HR>TargetSite=[" + ex.TargetSite + "]"; 
            	
                throw new Exception(cMessage);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return oAl;
        }
        public static void GenerateSqlInserts(string cPath,string sConnection,string cSql,string sTargetTableName,bool clearbef,bool compress)
        {

            string cFile=sTargetTableName+".sql";
            if (cPath!="")
            {
                if (outputfile=="")
                {
                	cFile=cPath+"\\"+sTargetTableName+".sql";
              	}else{
                	cFile=cPath+"\\"+outputfile;
              	}
            }else{
                if (outputfile!="")
                {
                	cFile=outputfile;
              	}
            	
            }
						
            _COLUMN_Data = new Dictionary<string, string>();
            _Type_Data = new Dictionary<string, string>();

            string cCOLUMNS="";
            if (File.Exists(sTargetTableName+".INI"))
            {

                StreamReader sr = new StreamReader(sTargetTableName+".INI");
                string s="";
                string cName="";
                string cValue="";
                while ((s = sr.ReadLine()) != null)
                {
                    if (s.IndexOf("=") > 0)
                    {
                        int cPositionB = s.IndexOf("=");

                        cName = s.Substring(0, cPositionB);
                        cValue = s.Substring(cPositionB + 1, s.Length - cPositionB - 1);
                        cValue=cValue.Trim();
                        _COLUMN_Data[cName]=cValue;
                        if (cName!="{TABLE_NAME}")
                        {
                            if (cCOLUMNS!="" )
                            {
                                cCOLUMNS=cCOLUMNS+",";
                            }
                            cCOLUMNS=cCOLUMNS+cName;
                        }
                    }else if (s.IndexOf("~") > 0)
                    {
                        int cPositionB = s.IndexOf("~");

                        cName = s.Substring(0, cPositionB);
                        cValue = s.Substring(cPositionB + 1, s.Length - cPositionB - 1);
                        cValue=cValue.Trim();

                        if (cName!="{TABLE_NAME}")
                        {
                            if (cCOLUMNS!="" )
                            {
                                cCOLUMNS=cCOLUMNS+",";
                            }
                            cCOLUMNS=cCOLUMNS+cName+" AS "+cValue;
                            _COLUMN_Data[cValue]=cValue;
                        }
                    }

                }
                sr.Close();
            }

//            string Target=Target;
            if (File.Exists(Target+"_type.ini"))
            {

                StreamReader sr = new StreamReader(Target+"_type.ini");
                string s="";
                string cName="";
                string cValue="";
                while ((s = sr.ReadLine()) != null)
                {
                    if (s.IndexOf("=") > 0)
                    {
                        int cPositionB = s.IndexOf("=");

                        cName = s.Substring(0, cPositionB);
                        cValue = s.Substring(cPositionB + 1, s.Length - cPositionB - 1);
                        cValue=cValue.Trim();
                        _Type_Data[cName]=cValue;
                    }
                }
                sr.Close();
            }

            FILE_NAME_NDX=0;
            if (outputfile=="")
            {
		            for (int i=0;i<1000;i++)
		            {
		                if (i==0)
		                {
		                    if (File.Exists(cFile))
		                    {
		                        File.Delete(cFile);
		                    }
		                }else{
		                    if (File.Exists(cFile.ToLower().Replace(".sql","-"+String.Format("{0:0000}",i)+".sql")))
		                    {
		                        File.Delete(cFile.ToLower().Replace(".sql","-"+String.Format("{0:0000}",i)+".sql"));
		                    }
		                }
		            }
						}
            cSql= cSql.Replace("{COLUMNS}",cCOLUMNS);
						string Engine="";
            string ccc=cSql.Replace("{ORDERBY_COLUMNS}","");
            ArrayList aryColumns =GetDatabaseTableColumns(sConnection,"","",ccc);

            if (_Type_Data.ContainsKey("QuotationMarksStart"))
            {
                QuotationMarksStart=_Type_Data["QuotationMarksStart"];
            }
            if (_Type_Data.ContainsKey("QuotationMarksEnd"))
            {
                QuotationMarksEnd=_Type_Data["QuotationMarksEnd"];
            }
            if (_Type_Data.ContainsKey("Engine"))
            {
                Engine=_Type_Data["Engine"];
            }
            
            if (createtable)
            {
                string CREATE_SQL="";
                if (dropifexist)
                {
                    if (Target=="mssql")
                    {
                        //CREATE_SQL=CREATE_SQL+"DROP TABLE IF EXISTS "+QuotationMarksStart+sTargetTableName+QuotationMarksEnd+";\n\n";
                    }else if (Target=="mysql" || Target=="sqlite"){
                        CREATE_SQL=CREATE_SQL+"DROP TABLE IF EXISTS "+QuotationMarksStart+sTargetTableName+QuotationMarksEnd+";\n\n";
                    }
                }
                CREATE_SQL=CREATE_SQL+"CREATE TABLE "+QuotationMarksStart+sTargetTableName+QuotationMarksEnd+" (\n";
                StringBuilder sbColumns = new StringBuilder(string.Empty);

                //Console.WriteLine(Target);

                foreach (string colname in aryColumns)
                {
                    if (sbColumns.ToString() != string.Empty)
                    {
                        sbColumns.Append(",\n");
                    }

                    if (oData.ContainsKey(sTargetTableName+"_"+colname))
                    {
                        Column oColumn=oData[sTargetTableName+"_"+colname];

                        string cDataType=oColumn.Type;
                        string cType="";

                        if (_Type_Data.ContainsKey(cDataType))
                        {
                            cType=_Type_Data[cDataType];
                        }else{
                            if (_Type_Data.ContainsKey(oColumn.TypeName))
                            {
                                cType=_Type_Data[oColumn.TypeName];
                            }else{
                                cType=oColumn.TypeName;
                            }
                        }

                        if (Target=="mssql")
                        {

                        }else if (Target=="mysql"){

                        }

                        string column_width="";
                        if (cDataType=="System.String")
                        {
                            if (oColumn.Size>100000)
                            {
                                if (_Type_Data.ContainsKey(cDataType+"_m"))
                                {
                                    cType=_Type_Data[cDataType+"_m"];
                                }
                            }else{
                                column_width="(" + oColumn.Size + ")";
                            }
                        }
                        string cDefault="";
                        if (cDataType=="System.Decimal")
                        {
                            column_width="(" + oColumn.Precision +","+oColumn.Scale+ ")";
                            if (oColumn.Default!="")
                            {
                            	cDefault=" DEFAULT ("+oColumn.Default+")";
                            	if (Target=="mssql")
                            	{
                            		//cDefault=" DEFAULT ("+oColumn.Default+")";
                            	}else if (Target=="mysql")
                            	{
                            		cDefault=" DEFAULT "+oColumn.Default+"";
                            	}
                            }else{
                            	if (Target=="mysql")
                            	{
                            		cDefault=" DEFAULT NULL";
                            	}                            	
                            }
                        }
                        string cAllowDBNull=" NULL ";
                        //if (Target=="mssql")
                        //{
                        //cAllowDBNull=" NULL ";
                        //}
                        if (!oColumn.AllowNull)
                        {
                            cAllowDBNull=" NOT NULL ";
                        }
                        sbColumns.Append(" " +QuotationMarksStart+ colname +QuotationMarksEnd+ " "+cType);
                        sbColumns.Append(column_width);
                        if (Target=="mysql")
                        {
	                        sbColumns.Append(cAllowDBNull);
	                        sbColumns.Append(cDefault);
                      	}else{
	                        sbColumns.Append(cAllowDBNull);
	                        sbColumns.Append(cDefault);
                      	}
                    }

                }

                CREATE_SQL=CREATE_SQL+sbColumns.ToString(); 
                if (cP_K_COLUMNS_NAME!="")
                {
                    CREATE_SQL=CREATE_SQL+",\n CONSTRAINT PK_"+sTargetTableName+" PRIMARY KEY ("+cP_K_COLUMNS_NAME+")\n";
                }
                
                if (Target=="mysql" && Engine!="")
                {
                	
                	CREATE_SQL=CREATE_SQL+"\n    ) ENGINE="+Engine+";\n\n"; 
                }else
                {
                	CREATE_SQL=CREATE_SQL+"\n    );\n\n"; 
                }
                WriteFileContents(cFile, CREATE_SQL, false);
            }

            if (cSql.IndexOf("ORDER BY") < 0)
            {
                if (cP_K_COLUMNS_NAME!="")
                {
                    cSql= cSql+" ORDER BY "+cP_K_COLUMNS_NAME;
                }
            }

            string cContents= GenerateSqlInserts(cFile,sConnection,cSql, aryColumns, sTargetTableName,clearbef, compress);

            if (File.Exists(cFile))
            {
                WriteFileContents(cFile, cContents, false);
            }else{
                WriteFileContents(cFile, cContents, true);
            }

        }

        private static string GenerateSqlInserts(string cFile,string sConnection,string sQuery,ArrayList aryColumns, string sTargetTableName,bool clearbef,bool compress)
        {
            string sSqlInserts = string.Empty;
            StringBuilder sbSqlStatements = new StringBuilder(string.Empty);
            StringBuilder sbColumns = new StringBuilder(string.Empty);
            StringBuilder sbColumns2 = new StringBuilder(string.Empty);

            // create the columns portion of the INSERT statement						            
            foreach (string colname in aryColumns)
            {
                if (sbColumns.ToString() != string.Empty)
                {
                    sbColumns.Append(", ");
                }
                if (_COLUMN_Data.ContainsKey(colname))
                {
                    sbColumns.Append(QuotationMarksStart + _COLUMN_Data[colname] + QuotationMarksEnd);
                }else{
                    sbColumns.Append(QuotationMarksStart + colname + QuotationMarksEnd);
                }
            }

            //Console.WriteLine(sQuery);
            sbColumns2=sbColumns;
            if (compress)
            {
                sbColumns2 = new StringBuilder(string.Empty);
                sbColumns2.Append(">=>=>");
            }
            //int total=dtTable.Rows.Count;
            //Console.Write();
            int _row=0;
            int _rows=0;
            int _file_rows=0;

            string cTable_Name=sTargetTableName;

            if (_COLUMN_Data.ContainsKey("{TABLE_NAME}"))
            {
                cTable_Name=_COLUMN_Data["{TABLE_NAME}"];
            }

            if (clearbef)
            {
                sbSqlStatements.Append("DELETE FROM "+cTable_Name+";");
                sbSqlStatements.AppendLine();
                sbSqlStatements.AppendLine();
            }
            if (compress)
            {
                string s = string.Format("INSERT INTO "+QuotationMarksStart+"{0}"+QuotationMarksEnd+"({1}) ", cTable_Name, sbColumns.ToString());
                sbSqlStatements.Append(">=>=>"+s);
                sbSqlStatements.AppendLine();
                sbSqlStatements.AppendLine();
            }
            string _isql = string.Format("INSERT INTO "+QuotationMarksStart+"{0}"+QuotationMarksEnd+"({1}) ", cTable_Name, sbColumns.ToString());

            if (compress)
            {
                _isql=">=>=>";
            }
            string sDatabase="";
            SqlConnection connection = null;
            int total=0;
            //DataTable table = null;
            try
            {
                connection = GetConnection(sConnection, sDatabase);
                if (connection == null)
                {
                    return "";
                }

                SqlCommand cmd = new SqlCommand(sQuery, connection);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.HasRows)
                {
                    total=dr.RecordsAffected;
                    // loop thru each record of the datatable
                    while (dr.Read())
                        //foreach (DataRow drow in dtTable.Rows)
                    {
                        // loop thru each column, and include the value if the column is in the array
                        StringBuilder sbValues = new StringBuilder(string.Empty);
                        foreach (string col in aryColumns)
                        {
                            if (sbValues.ToString() != string.Empty)
                                sbValues.Append(",");

                            // need to do a case to check the column-value types(quote strings(check for dups first), convert bools)
                            string sType = string.Empty;
                            try
                            {
                                sType = dr[col].GetType().ToString();
                                switch (sType.Trim().ToLower())
                                {
                                    case "system.boolean":
                                        sbValues.Append((Convert.ToBoolean(dr[col]) == true ? "1" : "0"));
                                        break;

                                    case "system.string":
                                        if (Target=="mysql" || Target=="sqlite")
                                        {
                                            sbValues.Append(string.Format("'{0}'", QuoteSQLString(dr[col])));
                                        }else{
                                            sbValues.Append(string.Format("N'{0}'", QuoteSQLString(dr[col])));
                                        }
                                        break;

                                    case "system.datetime":
                                        string sDateTime = QuoteSQLString(dr[col]);
                                        if (Validation.IsDateTime(sDateTime) == true)
                                            sDateTime = System.DateTime.Parse(sDateTime).ToString("yyyy-MM-dd HH:mm:ss");
                                        else
                                            sDateTime = string.Empty;
                                        sbValues.Append(string.Format("'{0}'", sDateTime));
                                        break;

                                    case "system.byte[]":
                                        sbValues.Append(string.Format("'{0}'", Convert.ToBase64String((byte[])dr[col])));
                                        break;                                

                                    default:
                                        if (dr[col] == System.DBNull.Value)
                                            sbValues.Append("NULL");
                                        else
                                            sbValues.Append(Convert.ToString(dr[col]));
                                        break;
                                }
                            }
                            catch
                            {
                                sbValues.Append(string.Format("'{0}'", QuoteSQLString(dr[col])));
                            }
                        }

                        //   INSERT INTO Tabs(Name) 
                        //      VALUES('Referrals')
                        // write the insert line out to the stringbuilder

                        sbSqlStatements.Append(_isql);
                        sbSqlStatements.AppendLine();
                        sbSqlStatements.Append('\t');
                        string snewsql = string.Format("VALUES({0});", sbValues.ToString());
                        sbSqlStatements.Append(snewsql);
                        sbSqlStatements.AppendLine();
                        sbSqlStatements.AppendLine();
                        _row++;
                        _rows++;
                        _file_rows++;
                        if (_row>=1000)
                        {

														Console.SetCursorPosition(1, Console.CursorTop);
														Console.Write("Table "+sTargetTableName+" "+string.Format("{0,9}",_rows)+" Rows");

                            WriteFileContents(cFile, sbSqlStatements.ToString(), false);

                            sbSqlStatements = new StringBuilder(string.Empty);
                            _row=0;

                            if (_file_rows>=FILE_ROWS)
                            {
                                _file_rows=0;
                                FILE_NAME_NDX++;
                                if (compress)
                                {
                                    string s = string.Format("INSERT INTO "+QuotationMarksStart+"{0}"+QuotationMarksEnd+"({1}) ", cTable_Name, sbColumns.ToString());
                                    sbSqlStatements.Append(">=>=>"+s);
                                    sbSqlStatements.AppendLine();
                                    sbSqlStatements.AppendLine();
                                }
                            }
                        }
                    }
                    dr.Close();
                    sSqlInserts = sbSqlStatements.ToString();
										Console.SetCursorPosition(1, Console.CursorTop);
										Console.Write("Table "+sTargetTableName+" "+string.Format("{0,9}",_rows)+" Rows");
                    Console.WriteLine("");
                }
                //DataTable dt = new DataTable();
                //dt.Load(dr);

                //table = dt;
            }
            catch (Exception exception)
            {
                throw new Exception("GetDatabases error: " + exception.Message);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }			

            return sSqlInserts;
        }

        public static string QuoteSQLString(string str)
        {
            return str.Replace("'", "''");
        }

        public static string QuoteSQLString(object ostr)
        {
            return ostr.ToString().Replace("'", "''");
        }
    }
    public class Validation
    {
        /// <summary>
        /// Returns true if the given object is a valid number, or false if it's not
        /// </summary>
        /// <param name="sDateTime"></param>
        /// <returns></returns>
        public static bool IsNumeric(Object objValue)
        {
            bool _Valid = false;

            try
            {
                double y = Convert.ToDouble(objValue);
                _Valid = true;
                return _Valid;
            }
            catch
            {
                _Valid = false;
            }

            try
            {                
                int x = Convert.ToInt32(objValue);                
                _Valid = true;
                return _Valid;
            }
            catch
            {
                _Valid = false;
            }

            return _Valid;
        }

        /// <summary>
        /// Returns true if the given string is a valid date string, or false if it's not
        /// </summary>
        /// <param name="sDateTime"></param>
        /// <returns></returns>
        public static bool IsDateTime(string sDateTime)
        {
            bool bIsDateTime = false;

            try
            {
                System.DateTime.Parse(sDateTime);
                bIsDateTime = true;
            }
            catch
            {
                bIsDateTime = false;
            }

            return bIsDateTime;
        }
    }
}
